﻿CREATE VIEW dbo.v_TrackerInfo
AS
SELECT     dbo.t_Tracker.ID AS TrackerID, dbo.t_Tracker_Car.VIN, dbo.t_CarInfo.LoanID, dbo.t_Dealer.Name AS Dealer, dbo.t_Tracker.StatusID, 
                      dbo.t_Menu.MenuName AS Status, CASE StatusID WHEN dbo.Tracker_Status__ToBeReturned() 
                      THEN ' background-color :dimgray;color:darksalmon' WHEN dbo.Tracker_Status__ToBeTested() 
                      THEN ' background-color :dimgray;color:darksalmon' ELSE '' END AS style, v_Technician.FirstName + ' ' + v_Technician.LastName AS Installer, 
                      CONVERT(varchar, dbo.t_Technician_Tracker.DateStamp, 111) AS DateToInstaller
FROM         dbo.t_Technician_Tracker INNER JOIN
                      dbo.t_Technician AS v_Technician ON dbo.t_Technician_Tracker.TechnicianID = v_Technician.ID RIGHT OUTER JOIN
                      dbo.t_Tracker INNER JOIN
                      dbo.t_Menu ON dbo.t_Tracker.StatusID = dbo.t_Menu.MenuID ON dbo.t_Technician_Tracker.TrackerID = dbo.t_Tracker.ID FULL OUTER JOIN
                      dbo.t_Tracker_Car INNER JOIN
                      dbo.t_Car ON dbo.t_Tracker_Car.VIN = dbo.t_Car.VIN FULL OUTER JOIN
                      dbo.t_Loan INNER JOIN
                      dbo.t_CarInfo ON dbo.t_Loan.ID = dbo.t_CarInfo.LoanID INNER JOIN
                      dbo.t_Dealer_SalesPersons ON dbo.t_Loan.DealerSalesPersonID = dbo.t_Dealer_SalesPersons.ID INNER JOIN
                      dbo.t_Dealer ON dbo.t_Dealer_SalesPersons.DealerID = dbo.t_Dealer.ID ON dbo.t_Car.VIN = dbo.t_CarInfo.VIN ON 
                      dbo.t_Tracker.ID = dbo.t_Tracker_Car.TrackerID

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[21] 4[40] 2[28] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[50] 4[25] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[62] 2[13] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1[56] 3) )"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2[66] 3) )"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4[50] 3) )"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3) )"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[64] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 7
   End
   Begin DiagramPane = 
      PaneHidden = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "t_Technician_Tracker"
            Begin Extent = 
               Top = 14
               Left = 829
               Bottom = 108
               Right = 1041
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Tracker"
            Begin Extent = 
               Top = 6
               Left = 622
               Bottom = 117
               Right = 773
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Menu"
            Begin Extent = 
               Top = 269
               Left = 924
               Bottom = 378
               Right = 1086
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Tracker_Car"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 115
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Car"
            Begin Extent = 
               Top = 133
               Left = 218
               Bottom = 242
               Right = 369
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Loan"
            Begin Extent = 
               Top = 165
               Left = 632
               Bottom = 274
               Right = 812
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_CarInfo"
            Begin Extent = 
               Top = 117
               Left = 429
               Bottom = 226
               Right = 597', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_TrackerInfo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Dealer_SalesPersons"
            Begin Extent = 
               Top = 265
               Left = 470
               Bottom = 359
               Right = 621
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Dealer"
            Begin Extent = 
               Top = 267
               Left = 219
               Bottom = 376
               Right = 370
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_Technician"
            Begin Extent = 
               Top = 115
               Left = 1032
               Bottom = 224
               Right = 1183
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
      PaneHidden = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 10
         Width = 284
         Width = 1500
         Width = 2415
         Width = 1500
         Width = 2715
         Width = 1500
         Width = 1500
         Width = 1545
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      PaneHidden = 
      Begin ColumnWidths = 11
         Column = 4725
         Alias = 900
         Table = 1680
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_TrackerInfo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_TrackerInfo';

